﻿/*
#------------------------------------------------------------------------------
#-- View Name:		[dbo].[vwMaint_LogIndex]
#-- Purpose:		Provides a method for reviewing index maintenance, and
#--					compare the use of REBUILD vs REORGANIZE
#--	Last Update:	08/05/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		[dbo].[spMaint_IndexDefrag]
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwMaint_LogIndex]
AS

WITH indexes AS (
	SELECT		mnt_table_catalog,
				mnt_table_schema,
				mnt_table_name,
				SUBSTRING(SUBSTRING(mnt_results, 0, CHARINDEX('] ON [', mnt_results)), CHARINDEX('ALTER INDEX [', mnt_results) + 13, CHARINDEX('---', mnt_results)) mnt_index_name,
				mnt_action_date,
				CAST(CASE WHEN CHARINDEX('REBUILD', mnt_results, 0) > 0 THEN 1 ELSE 0 END as bit) mnt_action_is_rebuild,
				mnt_success,
				LTRIM(RTRIM(REPLACE(SUBSTRING(mnt_results, CHARINDEX('---', mnt_results), 8000), '---', ''))) mnt_comments
	FROM		[dbo].[tblMaint_Log] (NOLOCK)
	WHERE		mnt_action_type = 'index'
)
SELECT		*,
			(
			SELECT		COUNT(*)
			FROM		indexes
			WHERE		mnt_table_catalog = t1.mnt_table_catalog
						AND mnt_table_schema = t1.mnt_table_schema
						AND mnt_table_name = t1.mnt_table_name
						AND mnt_index_name = t1.mnt_index_name
						AND mnt_action_date BETWEEN DATEADD(dd, -7, t1.mnt_action_date) AND t1.mnt_action_date
						AND mnt_action_is_rebuild = 1
			) rebuilds_in_last_week
FROM		indexes t1
